<?xml version="1.0" encoding="iso-8859-1" ?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
	<head>
		<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
		<title>FusionCharts v3 Documentation</title>
		<link rel="stylesheet" href="Style.css" type="text/css" />
	</head>
	<body>
		<table width="98%" border="0" cellspacing="0" cellpadding="3" align="center">
			<tr>
				<td><h2 class="pageHeader">Using FusionCharts with ASP.NET (VB.NET) &gt; Plotting data from a 
						database
					</h2>
				</td>
			</tr>
			<tr>
				<td valign="top" class="text"><p>In this section, we'll show you how to use 
						FusionCharts and ASP.NET to plot charts from data contained in a database. 
						We'll create a pie chart to show &quot;Production by Factory&quot; using:
					</p>
					<ul>
						<li>
							<span class="codeInline">dataXML</span>
							method first.</li>
						<li>
							Thereafter, we'll convert this chart to use
							<span class="codeInline">dataURL</span>
							method.
						</li>
					</ul>
					<p>For the sake of ease, we'll use an Access Database. The database is present in
						<span class="codeInline">Download Package &gt; Code &gt; VB_NET &gt; DB </span>folder. 
						You can, however, use any database with FusionCharts including MS SQL, Oracle, 
						mySQL etc.
					</p>
					<p><strong>Before you go further with this page, we recommend you to please see the 
							previous section &quot;Basic Examples&quot; as we start off from concepts 
							explained in that page. </strong>
					</p>
					<p class="highlightBlock">The code examples contained in this page are present in
						<span class="codeInline">Download Package &gt; Code &gt; VB_NET &gt; DBExample </span>
						folder. The Access database is present in
						<span class="codeInline">Download Package &gt; Code &gt; VB_NET &gt;</span>
						<span class="codeInline">DB</span>.
					</p>
				</td>
			</tr>
			<tr>
				<td valign="top" class="text">&nbsp;</td>
			</tr>
			<tr>
				<td valign="top" class="header">Database Structure
				</td>
			</tr>
			<tr>
				<td valign="top" class="text">Before we code the ASP.NET pages to retrieve data, 
					let's quickly have a look at the database structure.
				</td>
			</tr>
			<tr>
				<td valign="top" class="text"><img src="Images/Code_DB.gif" width="372" height="124" /></td>
			</tr>
			<tr>
				<td valign="top" class="text"><p>The database contains just 2 tables:</p>
					<ol>
						<li>
							<span class="codeInline">Factory_Master</span>: To store the name and id of 
							each factory</li>
						<li>
							<span class="codeInline">Factory_Output</span>: To store the number of units 
							produced by each factory for a given date.</li>
					</ol>
					<p>For demonstration, we've fed some dummy data in the database. Let's now shift 
						our attention to the ASP.NET page that will interact with the database, fetch 
						data and then render a chart.
					</p>
				</td>
			</tr>
			<tr>
				<td valign="top" class="text">&nbsp;</td>
			</tr>
			<tr>
				<td valign="top" class="header">Building the ASP.NET Page for dataXML Method
				</td>
			</tr>
			<tr>
				<td valign="top" class="text">The ASP.NET page for
					<span class="codeInline">dataXML</span>
					method example is named as
					<span class="codeInline">BasicDBExample.aspx</span>
					(in
					<span class="codeInline">DBExample</span>
					folder). It contains the following code (<span class="codeInline">GetFactorySummaryChartHtml()</span> method from code behind page also reproduced below): </td>
			</tr>
			<tr>
				<td valign="top" class="codeBlock"><p>&lt;%@ Page language=&quot;vb&quot; Codebehind=&quot;BasicDBExample.aspx.vb&quot; AutoEventWireup=&quot;false&quot; Inherits=&quot;InfoSoftGlobal.InfoSoftGlobal.GeneralPages.ASP.NET.DBExample.BasicDBExample&quot; %&gt;
				  <br />
				  &lt;HTML&gt;<br />
&nbsp;&nbsp;&nbsp;&lt;HEAD&gt;<br />
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&lt;TITLE&gt;FusionCharts - Database Example &lt;/TITLE&gt;<br />
&nbsp;&nbsp;&nbsp;<strong>&nbsp;&nbsp;&nbsp;&lt;SCRIPT LANGUAGE=&quot;Javascript&quot; SRC=&quot;../../FusionCharts/FusionCharts.js&quot;&gt;&lt;/SCRIPT&gt;</strong><br />
&nbsp;&nbsp;&nbsp;&lt;/HEAD&gt;<br />
&nbsp;&nbsp;&nbsp;&lt;body&gt;<br />
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&lt;form id='form1' name='form1' method='post' runat=&quot;server&quot;&gt;<br />
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<strong>&nbsp;&nbsp;&lt;%=GetFactorySummaryChartHtml()%&gt;</strong><br />
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&lt;/form&gt;<br />
&nbsp;&nbsp;&nbsp;&lt;/body&gt;<br />
&lt;/HTML&gt;<br />
<br>
						<br>
						<br>
						Public Function <strong>GetFactorySummaryChartHtml</strong>() As String<br>
						&nbsp;&nbsp;<span class="codeComment">&nbsp;&nbsp;'In this example, we show how to connect FusionCharts to a database.<br>
&nbsp;&nbsp;&nbsp;&nbsp;'For the sake of ease, we've used an Access database which is present in<br>
&nbsp;&nbsp;&nbsp;&nbsp;'../DB/FactoryDB.mdb. It just contains two tables, which are linked to 
						each<br>
&nbsp;&nbsp;&nbsp;&nbsp;'other. <br>
&nbsp;&nbsp;&nbsp;&nbsp;'xmlData will be used to store the entire XML document generated</span><br>
						&nbsp;&nbsp;&nbsp;&nbsp;Dim xmlData As String<br>
						&nbsp;&nbsp;&nbsp;<span class="codeComment">&nbsp;'Generate the chart element</span><br>
						&nbsp;&nbsp;&nbsp;&nbsp;xmlData = "&lt;chart caption='Factory Output report' subCaption='By 
						Quantity' pieSliceDepth='30' showBorder='1' formatNumberScale='0' numberSuffix=' Units'&gt;"<br>
						&nbsp;&nbsp;&nbsp;&nbsp;<span class="codeComment">'Iterate through each factory</span><br>
						&nbsp;&nbsp;&nbsp;&nbsp;Dim factoryQuery As String = "select * from Factory_Master"<br>
						&nbsp;&nbsp;&nbsp;&nbsp;Dim connectin As OdbcConnection = 
						DbHelper.Connection(DbHelper.ConnectionStringFactory)<br>
						&nbsp;&nbsp;&nbsp;&nbsp;Dim factoryCommand As OdbcCommand = New OdbcCommand(factoryQuery, 
						connectin)<br>
						&nbsp;&nbsp;&nbsp;&nbsp;Dim adapter As OdbcDataAdapter = New OdbcDataAdapter(factoryCommand)<br>
						&nbsp;&nbsp;&nbsp;&nbsp;Dim table As DataTable = New DataTable<br>
						&nbsp;&nbsp;&nbsp;&nbsp;adapter.Fill(table)<br>
						&nbsp;&nbsp;&nbsp;&nbsp;For Each row As DataRow In table.Rows<br>
						&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Dim quantityQuery As String = ("select sum(Quantity) as TotOutput 
						from Factory_Output where FactoryId=" &amp;row("FactoryId").ToString)<br>
						&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Dim quantityCommand As OdbcCommand = New OdbcCommand(quantityQuery, 
						connectin)<br>
						&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;xmlData = (xmlData &amp;("&lt;set label='" _<br>
						&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&amp;(row("FactoryName").ToString &amp;("' value='" _<br>
						&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&amp;(quantityCommand.ExecuteScalar.ToString &amp;"' 
						/&gt;")))))<br>
						&nbsp;&nbsp;&nbsp;&nbsp;Next<br>
						&nbsp;&nbsp;&nbsp;&nbsp;connectin.Close()<br>
						&nbsp;&nbsp;&nbsp;&nbsp;xmlData = (xmlData &amp;"&lt;/chart&gt;")<br>
						&nbsp;&nbsp;&nbsp;<span class="codeComment">&nbsp;'Create the chart - Pie 3D Chart with data from xmlData </span><br>
						&nbsp;&nbsp;&nbsp;<strong>&nbsp;Return 
						InfoSoftGlobal.FusionCharts.RenderChart("../../FusionCharts/Pie3D.swf", "", 
						xmlData, "FactorySum", "600", "300", False, False)</strong><br>
						End Function<br>
					</p>
				</td>
			</tr>
			<tr>
				<td valign="top" class="text"><p>The following actions are taking place in this code:</p>
					<ol>
						<li>
							We first include
							<span class="codeInline">FusionCharts.js</span>
							JavaScript class to enable easy embedding of FusionCharts and call
							<span class="codeInline">GetFactorySummaryChartHtml()</span>
							method contained in code behind page.</li>
						<li>In code behind page <span class="codeInline">GetFactorySummaryChartHtml()</span> function, 
							we then open a connection to Access database.						</li>
						<li>
							Thereafter, we generate the XML data document by iterating through the 
							recordset and store it in
							<span class="codeInline">xmlData</span>
							variable.						</li>
						<li>
							Finally, we render the chart using
							<span class="codeInline">Functions.RenderChart()</span>
							method and pass
							<span class="codeInline">xmlData</span>
							as
							<span class="codeInline">dataXML</span>.						</li>
					</ol>
					<p>When you now run the code, you'll get an output as under:
					</p>
				</td>
			</tr>
			<tr>
				<td valign="top" class="text"><img src="Images/Code_DBOut.jpg" width="572" height="273" class="imageBorder" /></td>
			</tr>
			<tr>
				<td valign="top" class="text">&nbsp;</td>
			</tr>
			<tr>
				<td valign="top" class="header">Converting the example to use dataURL method
				</td>
			</tr>
			<tr>
				<td valign="top" class="text"><p>Let's now convert this example to use dataURL method. 
						As previously explained, in dataURL mode, you need two pages:</p>
					<ol>
						<li>
							<strong>Chart Container Page</strong> - The page which embeds the HTML code to 
							render the chart. This page also tells the chart where to load the data from. 
							We'll name this page as
							<span class="codeInline">Default.aspx</span>.
						</li>
						<li>
							<strong>Data Provider Page</strong> - This page provides the XML data to the 
							chart. We'll name this page as
							<span class="codeInline">PieData.aspx</span></li>
					</ol>
					<p class="highlightBlock">The pages in this example are contained in<span class="codeInline"> Download Package &gt; Code &gt; VB_NET &gt; DB_dataURL</span>
						folder.
					</p>
				</td>
			</tr>
			<tr>
				<td valign="top" class="text">&nbsp;</td>
			</tr>
			<tr>
				<td valign="top" class="header">Chart Container Page -
					<span class="codeInline">Default.aspx </span></td>
			</tr>
			<tr>
				<td valign="top" class="text"><span class="codeInline">Default.aspx</span>
					contains the following code (with code behind page) to render the chart:
				</td>
			</tr>
			<tr>
				<td valign="top" class="codeBlock">
					&lt;%@ Page language="c#" Codebehind="Default.aspx.vb" AutoEventWireup="false" 
					Inherits="InfoSoftGlobal.GeneralPages.ASP.NET.DB_dataURL._Default" %&gt;
					<br>
					&lt;HTML&gt;<br>
					&nbsp;&lt;HEAD&gt;<br>
					&nbsp;&nbsp;&lt;TITLE&gt;FusionCharts - dataURL and Database Example 
					&lt;/TITLE&gt;<br>
					<strong>&nbsp;&nbsp;&lt;SCRIPT LANGUAGE="Javascript" 
					SRC="../../FusionCharts/FusionCharts.js"&gt;&lt;/SCRIPT&gt;</strong><br>
					&nbsp;&lt;/HEAD&gt;<br>
					&nbsp;&lt;body&gt;<br>
					<strong>&nbsp;&nbsp;&nbsp;</strong>&lt;form id='form1' name='form1' method='post' runat=&quot;server&quot;&gt;<br />
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<strong>&lt;%=GetQuantityChartHtml()%&gt;</strong><br />
&nbsp;&nbsp;&nbsp;&lt;/form&gt;<br>
					&nbsp;&lt;/body&gt;<br>
					&lt;/HTML&gt;<br>
					<br>
					<br>
					<strong>Public Function GetQuantityChartHtml() As String</strong><br>
					<span class="codeComment">&nbsp;'In this example, we show how to connect FusionCharts to a database <br>
&nbsp;'using dataURL method. In our other examples, we've used dataXML method<br>
&nbsp;'where the XML is generated in the same page as chart. Here, the XML data<br>
&nbsp;'for the chart would be generated in PieData.aspx.<br>
&nbsp;'To illustrate how to pass additional data as querystring to dataURL, <br>
&nbsp;'we've added an animate property, which will be passed to PieData.aspx. <br>
&nbsp;'PieData.aspx would handle this animate property and then generate the <br>
&nbsp;'XML accordingly.<br>
&nbsp;'For the sake of ease, we've used an Access database which is present in<br>
&nbsp;'../DB/FactoryDB.mdb. It just contains two tables, which are linked to 
					each<br>
&nbsp;'other.<br>
&nbsp;'Variable to contain dataURL<br>
&nbsp;'Set DataURL with animation property to 1<br>
&nbsp;'NOTE: It's necessary to encode the dataURL if you've added parameters to 
					it</span><br>
					&nbsp;Dim dataURL As String = 
					InfoSoftGlobal.FusionCharts.EncodeDataURL("PieData.aspx?animate=1", False)<br>
					&nbsp;<span class="codeComment">'Create the chart - Pie 3D Chart with dataURL as strDataURL</span><br>
					&nbsp;<strong>Return 
					InfoSoftGlobal.FusionCharts.RenderChart("../../FusionCharts/Pie3D.swf", 
					dataURL, "", "FactorySum", "600", "300", False, False)</strong><br>
					End Function<br>				</td>
			</tr>
			<tr>
				<td valign="top" class="text"><p>In the above code, we're:</p>
					<ol>
						<li>
							Including
							<span class="codeInline">FusionCharts.js</span>
							JavaScript class</li>
						<li>
							Create the
							<span class="codeInline">dataURL</span>
							string and store it in
							<span class="codeInline">dataURL</span>
							variable. We append a dummy propery
							<span class="codeInline">animate</span>
							to show how to pass parameters to
							<span class="codeInline">dataURL</span>. After building the
							<span class="codeInline">dataURL</span>, we encode it using
							<span class="codeInline">EncodeDataURL</span>
							function defined in
							<span class="codeInline">FusionCharts class </span>.
						</li>
						<li>
							Finally, we render the chart using
							<span class="codeInline">RenderChart()</span>
							method and set
							<span class="codeInline">dataURL</span>
							as
							<span class="codeInline">dataURL</span>.						</li>
					</ol>
				</td>
			</tr>
			<tr>
				<td valign="top" class="header">Creating the data provider page
					<span class="codeInline">PieData.aspx </span></td>
			</tr>
			<tr>
				<td valign="top" class="text">PieData.aspx contains the following code to output 
					XML Data:
				</td>
			</tr>
			<tr>
				<td valign="top" class="codeBlock"><p>
						Private Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)<br>
						&nbsp;If Not IsPostBack Then<br>
						&nbsp;<span class="codeComment">&nbsp;'This page generates the XML data for the Pie Chart contained in<br>
&nbsp;&nbsp;'Default.aspx. <br>
&nbsp;&nbsp;'For the sake of ease, we've used an Access database which is 
						present in<br>
&nbsp;&nbsp;'../DB/FactoryDB.mdb. It just contains two tables, which are linked 
						to each<br>
&nbsp;&nbsp;'other. <br>
&nbsp;&nbsp;'Database Objects - Initialization</span><br>
						&nbsp;&nbsp;Dim query As String<br>
						&nbsp;&nbsp;<span class="codeComment">'xmlData will be used to store the entire XML document generated</span><br>
						&nbsp;&nbsp;Dim xmlData As String = String.Empty<br>
						&nbsp;&nbsp;<span class="codeComment">'Default.aspx has passed us a property animate. We request that.</span><br>
						&nbsp;&nbsp;Dim animateChart As String<br>
						&nbsp;&nbsp;animateChart = Request.QueryString("animate")<br>
						&nbsp;&nbsp;<span class="codeComment">'Set default value of 1</span><br>
						&nbsp;&nbsp;If ((Not (animateChart) Is Nothing) _<br>
						AndAlso (animateChart.Length = 0)) Then<br>
						&nbsp;&nbsp;&nbsp;&nbsp;animateChart = "1"<br>
						&nbsp;&nbsp;End If<br>
						&nbsp;&nbsp;<span class="codeComment">'Create the recordset to retrieve data<br>
&nbsp;&nbsp; 'Generate the chart element</span><br>
						&nbsp;&nbsp;xmlData = ("&lt;chart caption='Factory Output report' 
						subCaption='By Quantity' pieSliceDepth='30' showBorder='1' formatNumberScale='0' numberSuffix=' Units' animation=' " _ + 
						(animateChart + "'&gt;"))<br>
						&nbsp;<span class="codeComment">&nbsp;'Iterate through each factory</span><br>
						&nbsp;&nbsp;query = "select * from Factory_Master"<br>
						&nbsp;&nbsp;Dim connection As OdbcConnection = 
						DbHelper.Connection(DbHelper.ConnectionStringFactory)<br>
						&nbsp;&nbsp;Dim command As OdbcCommand = New OdbcCommand(query, connection)<br>
						&nbsp;&nbsp;Dim factoryTable As DataTable = New DataTable<br>
						&nbsp;&nbsp;Dim factoryAdapter As OdbcDataAdapter = New 
						OdbcDataAdapter(command)<br>
						&nbsp;&nbsp;factoryAdapter.Fill(factoryTable)<br>
						&nbsp;&nbsp;For Each row As DataRow In factoryTable.Rows<br>
						&nbsp;&nbsp;&nbsp;&nbsp; Dim outputQuery As String = ("select sum(Quantity) as 
						TotOutput from Factory_Output where FactoryId=" + row("FactoryId").ToString)<br>
						&nbsp;&nbsp;&nbsp;&nbsp; Dim outputCommand As OdbcCommand = New 
						OdbcCommand(outputQuery, connection)<br>
						&nbsp;&nbsp;&nbsp;&nbsp; xmlData = (xmlData + ("&lt;set label='" _<br>
						&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; + (row("FactoryName").ToString + ("' 
						value='" _<br>
						&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; + (outputCommand.ExecuteScalar.ToString + 
						"' /&gt;")))))<br>
						&nbsp;&nbsp;Next<br>
						&nbsp;<span class="codeComment">&nbsp;'Finally, close &lt;chart&gt; element</span><br>
						&nbsp;&nbsp;xmlData = (xmlData + "&lt;/chart&gt;")<br>
						&nbsp;<span class="codeComment">&nbsp;'Set Proper output content-type</span><br>
						&nbsp;&nbsp;Response.ContentType = "text/xml"<br>
						&nbsp;<span class="codeComment">&nbsp;'Just write out the XML data<br>
&nbsp;&nbsp;'NOTE THAT THIS PAGE DOESN'T CONTAIN ANY HTML TAG, WHATSOEVER</span><br>
						&nbsp;&nbsp;Response.Write(xmlData)<br>
						&nbsp;End If<br>
						End Sub<br>
					</p>
				</td>
			</tr>
			<tr>
				<td valign="top" class="text"><p>In the above page:</p>
					<ol>
						<li>
							We first request the animate property which has been passed to it (from
							<span class="codeInline">dataURL</span>)</li>
						<li>
							We generate the data and store it in
							<span class="codeInline">xmlData</span>
							variable</li>
						<li>
							Finally, we write this data to output stream without any HTML tags.
						</li>
					</ol>
					<p>When you view this page, you'll get the same output as before.
					</p>
				</td>
			</tr>
		</table>
	</body>
</html>
